package de.ifi.lmu.snitzelhunt.database;

import java.util.Date;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import de.ifi.lmu.snitzelhunt.basics.Route;
import de.ifi.lmu.snitzelhunt.util.GSonUtility;

/**
 * Helper Class for the Database Table in the local SQL Database
 * 
 * @author jaschkowitz
 * 
 */
public class RouteTable {

	public static final String TABLE_NAME = "ROUTE";
	public static final String COLUMN_NAME_LOCAL_ID = "LOCAL_ID";
	public static final String COLUMN_NAME_GLOBAL_ID = "GLOBAL_ID";
	public static final String COLUMN_NAME_CREATOR = "CREATOR";
	public static final String COLUMN_NAME_NAME = "NAME";
	public static final String COLUMN_NAME_TASKS = "TASK_JSON";
	public static final String COLUMN_NAME_USERS = "USER_JSON";
	public static final String COLUMN_NAME_STARTDATE = "STARTDATE";
	public static final String COLUMN_NAME_ENDDATE = "ENDDATE";

	// Delete String
	public static final String SQL_DELETE_TABLE = "DROP TABLE IF EXISTS "
			+ TABLE_NAME;

	// Create String
	public static final String SQL_CREATE_TABLE = "CREATE TABLE " + TABLE_NAME
			+ "( " + COLUMN_NAME_LOCAL_ID
			+ " integer primary key autoincrement, " + COLUMN_NAME_GLOBAL_ID
			+ " integer, " + COLUMN_NAME_CREATOR + " Text, " + COLUMN_NAME_NAME
			+ " Text , " + COLUMN_NAME_TASKS + " TEXT, " + COLUMN_NAME_USERS
			+ " TEXT, " + COLUMN_NAME_STARTDATE + " integer, "
			+ COLUMN_NAME_ENDDATE + " integer" + ")";

	/**
	 * This method get the Route corresponding to localID from the local SQL
	 * Database
	 * 
	 * @param sqldb
	 * @param localID
	 * @return
	 */
	public static Route getRoute(SQLiteDatabase sqldb, Long localID) {
		Route loadRoute = new Route();

		// select SQL query and create Cursor for results
		Cursor cur = sqldb.rawQuery(
				"SELECT * FROM " + RouteTable.TABLE_NAME + " WHERE "
						+ RouteTable.COLUMN_NAME_LOCAL_ID + " = " + localID,
				null);

		// cursor can only have one result because localID is primary key
		if (cur != null) {
			if (cur.moveToFirst()) {
				long globalID = cur
						.getLong(cur
								.getColumnIndexOrThrow(RouteTable.COLUMN_NAME_GLOBAL_ID));
				String name = cur.getString(cur
						.getColumnIndexOrThrow(RouteTable.COLUMN_NAME_NAME));
				String creator = cur.getString(cur
						.getColumnIndexOrThrow(RouteTable.COLUMN_NAME_CREATOR));
				String task_json = cur.getString(cur
						.getColumnIndexOrThrow(RouteTable.COLUMN_NAME_TASKS));
				String user_json = cur.getString(cur
						.getColumnIndexOrThrow(RouteTable.COLUMN_NAME_USERS));
				long unix_startTime = cur
						.getLong(cur
								.getColumnIndexOrThrow(RouteTable.COLUMN_NAME_STARTDATE));
				long unix_endTime = cur.getLong(cur
						.getColumnIndexOrThrow(RouteTable.COLUMN_NAME_ENDDATE));
				loadRoute.setGlobal_routeId(globalID);
				loadRoute.setTasks(GSonUtility.deserializeTaskList(task_json));
				loadRoute.setCreator(creator);
				loadRoute.setName(name);
				loadRoute.setLocal_routeID(localID);
				loadRoute.setUsers(GSonUtility.deserializeUserList(user_json));

				loadRoute.setStartDate(new Date(unix_startTime * 1000));
				loadRoute.setEndDate(new Date(unix_endTime * 1000));
			}
		}
		return loadRoute;
	}

	/**
	 * This method inserts the Route in the local SQL Database
	 * 
	 * @param sqldb
	 * @param route
	 * @return
	 */
	public static long insertRoute(SQLiteDatabase sqldb, Route route) {

		// the fields that are inserted in the Database
		ContentValues insertValues = new ContentValues();
		insertValues.put(RouteTable.COLUMN_NAME_GLOBAL_ID,
				route.getGlobal_routeId());
		insertValues.put(RouteTable.COLUMN_NAME_NAME, route.getName());
		insertValues.put(RouteTable.COLUMN_NAME_CREATOR, route.getCreator());
		insertValues.put(RouteTable.COLUMN_NAME_TASKS,
				GSonUtility.serializeTaskList(route.getTasks()));
		insertValues.put(RouteTable.COLUMN_NAME_USERS,
				GSonUtility.serializeUserList(route.getUsers()));

		long unix_startTime = route.getStartDate().getTime() / 1000;
		long unix_endTime = route.getEndDate().getTime() / 1000;

		insertValues.put(RouteTable.COLUMN_NAME_STARTDATE, unix_startTime);
		insertValues.put(RouteTable.COLUMN_NAME_ENDDATE, unix_endTime);

		// SQL Insert Statement
		long localRouteID = sqldb.insert(RouteTable.TABLE_NAME, null,
				insertValues);
		Log.i("snitzelhunt util", "inserted Route in Row: " + localRouteID);
		route.setLocal_routeID(localRouteID);

		return localRouteID;
	}

	/**
	 * This method updates the Route in the local SQL Database
	 * 
	 * @param sqldb
	 * @param route
	 */
	public static void updateLocalRoute(SQLiteDatabase sqldb, Route route) {
		// the fields that are updated in the corresponding row in the Database
		ContentValues updateValues = new ContentValues();
		updateValues.put(RouteTable.COLUMN_NAME_GLOBAL_ID, route.getGlobal_routeId());
		updateValues.put(RouteTable.COLUMN_NAME_NAME, route.getName());
		updateValues.put(RouteTable.COLUMN_NAME_CREATOR, route.getCreator());
		updateValues.put(RouteTable.COLUMN_NAME_TASKS,
				GSonUtility.serializeTaskList(route.getTasks()));
		updateValues.put(RouteTable.COLUMN_NAME_USERS,
				GSonUtility.serializeUserList(route.getUsers()));
		long unix_startTime = route.getStartDate().getTime() / 1000;
		long unix_endTime = route.getEndDate().getTime() / 1000;

		updateValues.put(RouteTable.COLUMN_NAME_STARTDATE, unix_startTime);
		updateValues.put(RouteTable.COLUMN_NAME_ENDDATE, unix_endTime);

		// SQL Update Statement
		long row_count = sqldb.update(
				RouteTable.TABLE_NAME,
				updateValues,
				RouteTable.COLUMN_NAME_LOCAL_ID + " = "
						+ route.getLocal_routeID(), null);
		Log.i("snitzelhunt util", "updated Local Route: " + row_count);
	}

	/**
	 * This method updates the Route in the local SQL Database
	 * 
	 * @param sqldb
	 * @param route
	 */
	public static void deleteLocalRoute(SQLiteDatabase sqldb, long localID) {
		// the fields that are updated in the corresponding row in the Database
		long row_count = sqldb.delete(TABLE_NAME, COLUMN_NAME_LOCAL_ID + " = "
				+ localID, null);
		Log.i("snitzelhunt util", "deleted Local Route: " + row_count);
	}
}
